package com.mars.common.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * SQL工具类
 *
 * @author 源码字节-程序员Mars
 */
public class SqlUtils {

    private static String connectionUrl = "jdbc:mysql://#ip:#port/#schema?serverTimezone=Asia/Shanghai";

    private static StringBuilder sb = new StringBuilder();

    static {
        sb.append("select isc.table_name, isc.column_name, isc.column_type, isc.is_nullable, isc.column_key, isc.column_comment, ist.table_comment, ist.create_time ")
                .append("from information_schema.columns isc ")
                .append("join information_schema.tables ist on isc.table_name = ist.table_name and ist.table_schema = '#schema' ")
                .append("where isc.table_schema = '#schema'");
        try {
            //1.加载数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            LoggerUtils.error("加载数据库驱动异常", e);
        }
    }

    public static List<Map<String, String>> selectTableInfo(String ip, String port, String schema, String user, String password) {
        List<Map<String, String>> result = new ArrayList<>();
        String url = connectionUrl.replace("#ip", ip).replace("#port", port).replace("#schema", schema);
        String selectSql = sb.toString().replaceAll("#schema", schema);
        try (
                //2.获取数据库连接
                Connection connection = DriverManager.getConnection(url, user, password);
                //3.获取Statement对象
                Statement statement = connection.createStatement();
                //4.执行sql
                ResultSet resultSet = statement.executeQuery(selectSql);
        ) {
            //5.处理结果集
            while (resultSet.next()) {
                Map<String, String> map = new HashMap<>(8 , 1);
                map.put("tableName", resultSet.getString("table_name"));
                map.put("columnName", resultSet.getString("column_name"));
                map.put("columnType", resultSet.getString("column_type"));
                map.put("isNullable", resultSet.getString("is_nullable"));
                map.put("columnKey", resultSet.getString("column_key"));
                map.put("columnComment", resultSet.getString("column_comment"));
                map.put("tableComment", resultSet.getString("table_comment"));
                map.put("createTime", resultSet.getString("create_time"));
                result.add(map);
            }
            //6.释放资源
        } catch (SQLException e) {
            LoggerUtils.error("获取表信息异常", e);
        }
        return result;
    }
}
